FULLTEXTTABLE
FUNCTION
GENERAL
GET
GLOBAL
GO
GOTO
GRANT
GROUP
GROUPING
HAVING
HOLDLOCK
HOST
HOUR
HOUR
IDENTITY
IDENTITY_INSERT
IDENTITYCOL
IF
IGNORE
IMMEDIATE
IN
INCLUDE
INDEX
INDICATOR
INITIALIZE
INITIALLY
INNER
INOUT
INPUT
INSENSITIVE
INSERT
INT
INTEGER
INTERSECT
INTERVAL
INTO
IS
ISOLATION
ITERATE
JOIN
KEY
KILL
LANGUAGE
LARGE
LAST
LATERAL
LEADING
LEFT
LESS
LEVEL
LIKE
LIMIT
LINENO
LOAD
LOCAL
LOCALTIME
LOCALTIMESTAMP
LOCATOR
LOWER
MAP
MATCH
MAX
MIN
MINUTE
MODIFIES
MODIFY
MODULE
MONTH
NAMES
NATIONAL
NATURAL
NCHAR
NCLOB
NEW
NEXT
NO
NOCHECK
NONCLUSTERED
NONE
NOT
NULL
NULLIF
NUMERIC
OBJECT
OCTET_LENGTH
OF
OFF
OFFSETS
OLD
ON
ONLY
OPEN
OPENDATASOURCE
OPENQUERY
OPENROWSET
OPENXML
OPERATION
OPTION
OR
ORDER
ORDINALITY
OUT
OUTER
OUTPUT
OVER
OVERLAPS
PAD
PARAMETER
PARAMETERS
PARTIAL
PASCAL
PATH
PERCENT
PLAN
POSITION
POSTFIX
PRECISION
PREFIX
PREORDER
PREPARE
PRESERVE
PRIMARY
PRINT
PRIOR
PRIVILEGES
PROC
PROCEDURE
PUBLIC
RAISERROR
READ
READS
READTEXT
REAL
RECONFIGURE
RECURSIVE
REF
REFERENCES
REFERENCING
RELATIVE
REPLICATION
RESTORE
RESTRICT
RESULT
RETURN
RETURNS
REVOKE
RIGHT
ROLE
ROLLBACK
ROLLUP
ROUTINE
ROW
ROWCOUNT
ROWGUIDCOL
ROWS
RULE
SAVE
SAVEPOINT
SCHEMA
SCOPE
SCROLL
SEARCH
SECOND
SECTION
SELECT
SEQUENCE
SESSION
SESSION_USER
SET
SETS
SETUSER
SHUTDOWN
SIZE
SMALLINT
SOME
SPACE
SPECIFIC
SPECIFICTYPE
SQL
SQLCA
SQLCODE
SQLERROR
SQLEXCEPTION
SQLSTATE
SQLWARNING
START
STATE
STATEMENT
STATIC
STATISTICS
STRUCTURE
SUBSTRING
SUM
SYSTEM_USER
TABLE
TEMPORARY
TERMINATE
TEXTSIZE
THAN
THEN
TIME
TIMESTAMP
TIMEZONE_HOUR
TIMEZONE_MINUTE
TO
TOP
TRAILING
TRAN
TRANSACTION
TRANSLATE
TRANSLATION
TREAT
TRIGGER
TRIM
TRUE
TRUNCATE
TSEQUAL
UNDER
UNION
UNIQUE
UNKNOWN
UNNEST
UPDATE
UPDATETEXT
UPPER
USAGE
USE
USER
USING
VALUE
VALUES
VARCHAR
VARIABLE
VARYING
VIEW
WAITFOR
WHEN
WHENEVER
WHERE
WHILE
WITH
WITHOUT
WORK
WRITE
WRITETEXT
YEAR
ZONE
Index
Symbols
* (asterisk)
multiplication operator, 80
wildcard character, 3536, 101
@ (at symbol), 214, 233
[ ] (brackets), 6970, 275
/ (division) operator, 80
= (equality) operator, 51
> (greater than) operator, 51
>= (greater than or equal to) operator, 51
< (less than) operator, 51, 52
<= (less than or equal to) operator, 51
<> (nonequality) operator, 51, 53
!= (nonequality) operator, 51, 53
!> (not greater than) operator, 51
!< (not less than) operator, 51
( ) (parentheses), 61
% (percent sign)
modulo operator, 80
wildcard character, 6668
+ (plus sign)
addition operator, 80
concatenation operator, 7477
; (semicolon), 33, 179180
(single quotes), 53, 169, 220
- (subtraction) operator, 80
_ (underscore), 6869
A
Abs() function, 94
ABSOLUTE keyword, 243
absolute value, returning, 94
access control
access rights, 291293
explained, 287288
logins, renaming, 291
user accounts
creating, 290
deleting, 290
enabling/disabling, 290
user management, 289
access rights, 291293
accessing SQL Server, 1617
accounts (user)
creating, 290
deleting, 290
enabling/disabling, 290
addition operator (+), 80
administrative login, 19
AFTER triggers, 249
aggregate functions
Avg(), 9899
combining, 106107
Count(), 100101, 106
distinct values, 105
explained, 9798
with joins, 148150
Max(), 101102, 106
Min(), 102103
Sum(), 103104
aliases
calculated field aliases, 7778
naming, 107
table aliases, 141142
all columns, retrieving, 3536
ALL keyword, 105
ALTER FULLTEXT statement, 165166
ALTER LOGIN statement, 290291
ALTER TABLE statement, 199200, 280, 291
ALTER TRIGGER statement, 249
altering tables, 199200
AND operator, 5758, 170
application level, filtering data at, 50
ASC keyword, 4647
ascending order, sorting data in, 4446
assigning
search term weights, 173
values to variables, 215216
asterisk (*)
multiplication operator, 80
wildcard character, 3536, 101
AUTO keyword, 266
autocommits, 259, 261
averages, returning, 9899
Avg() function, 9899
B
batch statements, 219
BEGIN statement, 223224
BETWEEN operator, 51, 54
Boolean operators. See operators
brackets ([ ]), 6970, 275
BREAK statement, 226
C
calculated fields
aliases, 7778
concatenating, 7477
explained, 7374
mathematical calculations, 7980
subqueries as, 123126
views, 210211
Cartesian products, 133137
case sensitivity
collation sequences, 281283
data sorting, 46
searches, 67
statements, 33
Cast() function, 270
catalogs, full-text
creating, 163
dropping, 165166
managing, 165166
character sets
definition of, 277
Unicode, 283285
CharIndex() function, 85
checking
for existance, 126128
for nonmatches, 53
for NULL values, 5455
for range of values, 54
against single values, 5152
FROM clause, 133
clauses. See also keywords; statements
ELSE, 222
FOR JSON AUTO, 273
FOR XML, 264268
FROM, 133
FULL OUTER JOIN, 148
GROUP BY, 110111, 115116
HAVING, 111114
INNER JOIN, 137138
JSON PATH, 274
ORDER BY
example, 115116
sorting by multiple columns, 4344
sorting by single column, 4143
order of, 117
OUTER JOIN, 146147
OUTPUT
DELETE statement, 188189
INSERT statement, 183184
UPDATE statement, 188189
WHERE
AND operator, 5758
brackets ([ ]) wildcard, 6970
comparison operators. See comparison operators
joins, 133137
LIKE operator, 6566
nonmatches, checking for, 53
NULL values, checking for, 5455
percent sign (%) wildcard, 6668
range of values, checking for, 54
single values, checking against, 5152
subqueries, 119128
syntax, 4950
underscore (_) wildcard, 6869
views, 209
client server software, 1415
client utilities, 1516
CLOSE statement, 241242
closing cursors, 241242
COLLATE keyword, 281283
collations
case sensitivity, 281283
collation sequences, 278280
definition of, 277
importance of, 278
columns. See also fields
aliases, 7778
average value of, 9899
deleting
deleted data, reporting on, 188189
guidelines, 188189
syntax, 187188
explained, 78
fully qualified column names, 133
identity columns, 28
maximum value, 101102
minimum value, 102103
omitting from INSERT operations, 179
returning list of, 2728
sorting by
multiple columns, 4344
single columns, 4143
totals, 103104
updating
guidelines, 188189
syntax, 185187
updated data, reporting on, 188189
combined queries
creating, 154157
duplicate rows, including/eliminating, 157158
explained, 153
sorting results of, 158159
combining aggregate functions, 106107
comments, 220
COMMIT statement, 259
commits
autocommits, 259, 261
explicit commits, 259
comparison operators
BETWEEN operator, 54
equality (=) operator, 51
IS NULL operator, 5455
less than (<) operator, 52
nonequality (<>) operator, 53
nonequality (!=) operator, 53
table of, 4950
complete rows, inserting, 176179
concatenating fields, 7477
concatenation (+) operator, 7477
conditional processing
BEGIN statement, 223224
END statement, 223224
IF statement, 221223
WHILE statement, 225226
configuring full-text searching
full-text catalogs, 163166
full-text indexes, 164166
full-text search support, 162163
connecting to SQL Server, 1920
CONTAINS keyword, 168172
CONTAINSTABLE() function, 172173
contents of variables, viewing, 216217
CONTINUE statement, 226
controlling access. See access control
converting
numeric values to strings, 85
strings to lowercase, 85
strings to uppercase, 85
variables to strings, 217
correlated subqueries, 125126
Cos() function, 94
cosine, returning, 94
Count() function
DISTINCT keyword, 100101
explained, 100101
as subqueries, 124125
counting rows, 100101
CREATE FULLTEXT CATALOG statement, 163
CREATE FULLTEXT INDEX statement, 164
CREATE LOGIN statement, 290
CREATE PROCEDURE statement, 230231
CREATE TABLE statement
collation sequences, 280
DEFAULT keyword, 197198
default values, 197198
example, 269
IDENTITY keyword, 195197
NULL values, 193194
primary keys, 194195
syntax, 192193
CREATE TRIGGER statement, 248
CREATE VIEW statement, 205
create.sql file, 22
creating
calculated fields
aliases, 7778
concatenating, 7477
explained, 7374
mathematical calculations, 7980
combined queries, 154157
cursors, 240241
full-text catalogs, 163
full-text indexes, 164165
groups, 110111
joins, 132133
sample tables, 2223
stored procedures, 230231
tables
basic table creation, 192193
DEFAULT keyword, 197198
default values, 197198
explained, 191
IDENTITY keyword, 195197
NULL values, 193194
primary keys, 194195
triggers, 248
user accounts, 290
views, 205
cross joins, 136
cursors
closing, 241242
creating, 240241
deleting, 240241
explained, 239240
fetched data, 242245
opening, 241242
with WHILE loops, 225
D
data
cursor data, fetching, 242245
deleting
deleted data, reporting on, 188189
guidelines, 188189
syntax, 187188
filtering
at application level, 50
comparison operators, 4950
filter conditions, 5155
groups, 111114
IS NULL operator, 5455
multiple WHERE clauses, 5764
by subqueries, 119123
views, 209
WHERE clause syntax, 4950
wildcard filtering, 6570
formatting
client versus server formatting, 74
overview, 35
queries, 122
retrieved data, 207208
statements, 193
groups
creating, 110111
explained, 109110
filtering, 111114
grouping versus sorting, 114116
inserting
complete rows, 176179
explained, 175
list of changes, returning, 183184
multiple rows, 179180
retrieved data, 180183
JSON (JavaScript Object Notation) data
functions, 275
retrieving data as JSON, 273274
retrieving
all columns, 3536
distinct rows, 3637
fully qualified table names, 3940
individual columns, 3133
multiple columns, 3334
overview, 31
results, limiting, 3739
sorting
ascending order, 4647
case sensitivity, 46
descending order, 4446
dictionary sort order, 46
grouping versus sorting, 114116
by multiple columns, 4344
by single column, 4143
summarizing with aggregate functions
Avg(), 9899
Count(), 100101, 106
distinct values, 105
explained, 9798
Max(), 101102, 106
Min(), 101102
multiple aggregate functions, 106107
Sum(), 103104
updating
guidelines, 188189
syntax, 185187
updated data, reporting on, 188189
XML (Extensible Markup Language) data
retrieving, 264268
searching for, 271272
storing, 268270
support for, 263
XML schemas, 271
Database Engine Tuning Advisor, 296297
Database Management System (DBMS), 6. See also SQL Server
database servers, 14
DatabasePropertyEX() function, 279
databases. See also tables
definition of, 6
everyday uses of, 5
master databases, 25
returning list of, 26
scaling, 131
selecting, 2425
SYS, 289
datatypes
date time, 91
explained, 78
NCHAR, 283
NTEXT, 283
NVARCHAR, 283
parameter datatypes, 233
date manipulation functions, 8794
date time datatype, 91
DateAdd() function, 87
DateDiff() function, 87, 9193
DateName() function, 87
DatePart() function, 8790, 221
Day() function, 87, 9293
DBMS (Database Management System), 6. See also SQL Server
DEALLOCATE statement, 240241
debugger, 218
DECLARE statement, 214215, 233, 240241
declaring variables, 214215
DEFAULT keyword, 197198
default values
assigning to variables, 215
specifying, 197198
DELETE statement
DELETE FROM, 188
DELETE triggers, 252
deleted data, reporting on, 188189
guidelines, 188189
syntax, 187188
DELETE triggers, 252
deleted data, reporting on, 188189
deleting
cursors, 240241
data
deleted data, reporting on, 188189
guidelines, 188189
syntax, 187188
tables, 201
user accounts, 290
derived columns (aliases), 78
DESC keyword, 4446
descending order, sorting data in, 4446
dialogs, New Database, 23
dictionary sort order, 46
different tables, combining, 159
DISABLE TRIGGER statement, 250
disabling
INSERT statement, 175
triggers, 249250
user accounts, 290
DISTINCT keyword, 3637, 105106
distinct values, aggregates on, 105
division operator (/), 80
documentation, 297
downloading SQL Server, 17
DROP FULLTEXT statement, 165166
DROP LOGIN statement, 290
DROP PROCEDURE statement, 231232
DROP TABLE statement, 201
DROP TRIGGER statement, 249
DROP VIEW statement, 205
dropping
catalogs/indexes, 165166
logins, 290
stored procedures, 231232
tables, 201
triggers, 249
views, 205
duplicate rows in unions, including/eliminating, 157158
E
ELEMENTS keyword, 266267
eliminating duplicate rows in unions, 157158
ELSE clause, 222
empty strings, 194
ENABLE TRIGGER statement, 250
enabling
full-text searching, 162163
triggers, 249250
user accounts, 290
encodings, 277
END statement, 223224
equality (=) operator, 51
equijoins, 137
evaluation, order of, 5961, 80
EXEC statement, 230
EXECUTE statement, 230
executing stored procedures, 230
exist function, 271
existance, checking for, 126128
EXISTS keyword, 126128
Exp() function, 94
explicit commits, 259
EXPLICIT keyword, 267268
exponential values, returning, 94
F
FETCH statement, 242245
@@FETCH_STATUS function, 244
fetching data, 242245
fields. See also columns
calculated fields
aliases, 7778
concatenating, 7477
explained, 7374
mathematical calculations, 7980
subqueries as, 123126
views, 210211
identity fields, 176177
files
create.sql, 22
populate.sql, 22
filter conditions
comparison operators, 4950
multiple WHERE clauses
AND operator, 5758
IN operator, 6163
NOT operator, 6364
OR operator, 5859
order of evaluation, 5961
nonmatches, 53
NULL values, 5455
range of values, 54
single values, 5152
WHERE clause, 4950
wildcard filtering
brackets ([ ]) wildcard, 6970
case sensitivity, 67
LIKE operator, 6566
NULL values, 68
percent sign (%) wildcard, 6668
tips for, 71
underscore (_) wildcard, 6869
filtering data
at application level, 50
comparison operators, 4950
groups, 111114
IS NULL operator, 5455
multiple WHERE clauses
NOT operator, 6364
AND operator, 5758
OR operator, 5859
IN operator, 6163
order of evaluation, 5961
nonmatches, 53
NULL values, 5455
range of values, 54
single value, 5152
by subqueries, 119123
views, 209
WHERE clause syntax, 4950
wildcard filtering
brackets ([ ]) wildcard, 6970
case sensitivity, 67
LIKE operator, 6566
NULL values, 68
percent sign (%) wildcard, 6668
tips for, 71
trailing spaces, 68
underscore (_) wildcard, 6869
FIRST keyword, 243
fn_helpcollations() function, 278, 279280
foreign keys, 11, 130
formatting
client versus server formatting, 74
overview, 35
queries, 122
retrieved data, 207208
statements, 193
FORMSOF() function, 172
FOR XML clause, 264268
four-digit year format, 90
FREETEXT keyword, 166168
FULL OUTER JOIN clause, 148
full-text catalogs
creating, 163
managing, 165166
full-text indexes
creating, 164165
managing, 165166
full-text searching
enabling, 162163
explained, 161162
full-text catalogs
creating, 163
managing, 165166
full-text indexes
creating, 164165
managing, 165166
performance, 161
performing with CONTAINS, 168172
performing with FREETEXT, 166168
search results, ranking, 172173
FulltextCatalogProperty() function, 166
FULLTEXTTABLE() function, 172173
fully qualified column names, 133
fully qualified table names, 3940
functions
Abs(), 94
Avg(), 9899
Cast(), 270
CharIndex(), 85
CONTAINSTABLE(), 172173
Cos(), 94
Count()
DISTINCT keyword, 106
explained, 100101
as subqueries, 124125
DatabasePropertyEX(), 279
DateAdd(), 87
DateDiff(), 87, 9193
DateName(), 87
DatePart(), 8790, 221
Day(), 87, 9293
exist, 271
Exp(), 94
explained, 83
@@FETCH_STATUS, 244
fn_helpcollations(), 278, 279280
FORMSOF(), 172
FulltextCatalogProperty(), 166
FULLTEXTTABLE(), 172173
GetDate(), 87, 198, 221
@@IDENTITY system function, 197
ISABOUT(), 173
ISJSON(), 275
JSON_MODIFY(), 275
JSON_QUERY(), 275
JSON_VALUE(), 275
Left(), 85
Len(), 85
Lower(), 85
LTrim(), 77, 85
Max()
DISTINCT keyword, 106
explained, 101102
order numbers, determining, 196
Min(), 102103
modify, 271
Month(), 87, 9293
nodes, 271
Pi(), 94
portability, 83
query, 271
Rand(), 94
Replace(), 85
Right(), 85
Round(), 94
RTrim(), 7677, 85
ServerProperty(), 279
Sin(), 94
Soundex(), 8586
Sqrt(), 94
Square(), 94
Str(), 85
SubString(), 85
Sum(), 103104
Tan(), 94
testing, 80
types of
aggregate functions, 97107
date and time manipulation functions, 8794
numeric manipulation functions, 94
overview, 84
text manipulation functions, 8486
Upper(), 8485
value, 271
Year(), 87, 9293
G
GetDate() function, 198, 221
globalization
character sets
definition of, 277
Unicode, 283285
collations
case sensitivity, 281283
collation sequences, 278280
definition of, 277
importance of, 278
encodings, 277
GRANT statement, 291292
granting access rights, 291292
great than (>) operator, 51
greater than or equal to (>=) operator, 51
GROUP BY clause (SELECT statement), 110111, 115116
grouping statements, 223224
groups
creating, 110111
explained, 109110
filtering, 111114
grouping versus sorting, 114116
H
HAVING clause (SELECT statement), 111114
hostname, 20
I
identity, 28
identity fields, 176177
IDENTITY keyword, 195197
@@IDENTITY system function, 197
IF statement, 221223
IMPLICIT_TRANSACTIONS setting, 261
improving performance, 295297
including duplicate rows in unions, 157158
incrementally building queries, 128
indexes
full-text indexes
creating, 164165
managing, 165166
updating, 165
individual columns, retrieving, 3133
INNER JOIN clause, 137138
inner joins, 137
IN operator, 6163
INSERT statement
disabling, 175
INSERT SELECT, 180183
INSERT triggers, 251252
inserting complete rows, 176179
inserting multiple rows, 179180
inserting retrieved data, 180183
INTO keyword, 179
omitting columns from, 179
OUTPUT clause, 183184
overview, 175
returning list of changes, 183184
Unicode text, 284285
VALUES keyword, 177179
INSERT triggers, 251252
inserting data
complete rows, 176179
explained, 175
multiple rows, 179180
retrieved data, 180183
returning, 183184
installing SQL Server, 18
INSTEAD OF triggers, 249
integrity, referential, 131
intelligent stored procedures, 235238
IS NULL operator, 51, 5455
ISABOUT() function, 173
ISJSON() function, 275
J
JavaScript Object Notation. See JSON (JavaScript Object Notation)
joins
advantages of, 131
with aggregate functions, 148150
Cartesian products, 133137
creating, 132133
cross joins, 136
definition of, 129
equijoins, 137
guidelines, 150
inner joins, 137
multiple table joins, 138140
natural joins, 145
outer joins, 145148
performance considerations, 139
referential integrity, 131
self joins, 142144
simplifying with views, 206207
WHERE clause, 133137
JSON (JavaScript Object Notation)
functions, 275
retrieving data as JSON, 273274
FOR JSON AUTO clause, 273
JSON PATH clause, 274
JSON_MODIFY() function, 275
JSON_QUERY() function, 275
JSON_VALUE() function, 275
K
keys
foreign keys, 11, 130
primary keys
creating, 194195
definition of, 911, 130
and NULL values, 195
FROM keyword, 3133
AS keyword, 78
IN keyword, 155
INTO keyword, 179
keywords. See also clauses; statements
ABSOLUTE, 243
ALL, 105
AS, 78
ASC, 4647
AUTO, 266
COLLATE, 281283
CONTAINS, 168172
DEFAULT, 197198
DESC, 4446
DISTINCT, 3637, 105106
ELEMENTS, 266267
EXISTS, 126128
EXPLICIT, 267268
FIRST, 243
FREETEXT, 166168
FROM
individual columns, retrieving, 3133
multiple columns, retrieving, 3334
IDENTITY, 195197
IN, 155
INTO, 179
LAST, 243
LEFT, 146147
NEAR, 170171
NEXT, 243
NULL
aggregate functions and, 100103
checking for, 5455
CREATE TABLE statement, 193194
null value elimination warning, 150
UPDATE statement, 187
wildcards and, 68
OUTPUT
DELETE statement, 188189
INSERT statement, 183184
UPDATE statement, 188189
PERCENT, 3839
PRIOR, 243
RAW, 266267
RIGHT, 146147
TOP, 3739
UNION, 154157
VALUES, 177179
L
LAST keyword, 243
Left() function, 85
LEFT keyword, 146147
left outer joins, 147
Len() function, 85
less than (<) operator, 51, 52
less than or equal to (<=) operator, 51
LIKE operator, 6566
limiting results, 3739
listing
columns, 2728
databases, 26
inserted data, 183184
tables, 2627
triggers, 250
local servers, 17
localization
character sets
definition of, 277
Unicode, 283285
collations
case sensitivity, 281283
collation sequences, 278280
definition of, 277
importance of, 278
encodings, 277
locating SQL Server Management Studio, 18
logging in, 19
logins
dropping, 290
renaming, 291
sa (system administrator), 288
loops, WHILE, 225226
Lower() function, 85
lowercase, converting strings to, 85
LTrim() function, 77, 85
M
maintaining referential integrity, 131
Management Studio. See SQL Server Management Studio
managing
full-text catalogs, 165166
full-text indexes, 165166
users, 289
master databases, 25
mathematical calculations, 7980
mathematical operators, 80
Max() function
DISTINCT keyword, 106
explained, 101102
order numbers, determining, 196
maximum value, returning, 101102
Microsoft SQL Server Management Studio. See SQL Server Management Studio
Min() function, 102103
minimum value, returning, 102103
modify function, 271
Month() function, 87, 9293
multiple columns
retrieving, 3334
sorting by, 4344
multiple rows, inserting, 179180
multiple tables, joining, 138140
multiple WHERE clauses
AND operator, 5758
IN operator, 6163
NOT operator, 6364
OR operator, 5859
order of evaluation, 5961
multiplication (*) operator, 80
multistatement triggers, 252
N
N prefix, 285
names
alias names, 107
fully qualified column names, 133
savepoint names, 260
natural joins, 145
navigating SQL Server Management Studio, 2022
NCHAR datatype, 283
NEAR operator, 170171
New Database dialog, 23
New Query button, 2021
NEXT keyword, 243
nodes function, 271
non-ANSI outer joins, 147148
nonequality (<>) operator, 51, 53
nonequality (!=) operator, 51, 53
nonmatches, checking for, 53
nonnumeric data, aggregate functions and
Max(), 102
Min(), 103
not greater than (!>) operator, 51
not less than (!<) operator, 51
NOT operator, 6364
NTEXT datatype, 283
null value elimination warning, 150
NULL values
aggregate functions and
Avg(), 100
Count(), 101
Max(), 102
Min(), 103
Sum(), 104
checking for, 5455
compared to empty strings, 194
CREATE TABLE statement, 193194
null value elimination warning, 150
primary keys and, 195
UPDATE statement, 187
wildcards and, 68
numeric manipulation functions, 94
numeric values, converting to strings, 85
NVARCHAR datatype, 283
O
Object Explorer, 21
obtaining SQL Server, 17
omitting columns from INSERT operations, 179
OPEN statement, 241242
opening cursors, 241242
operators. See also keywords
AND, 5758, 170
comparison operators
BETWEEN operator, 54
equality operator (=), 51
IS NULL operator, 5455
less than operator (<), 52
nonequality operator (<>), 53
nonequality operator (!=), 53
table of, 4950
concatenation (+) operator, 7477
CONTAINS, 168172
FREETEXT, 166168
IN, 6163
LIKE, 6566
mathematical operators, 80
NEAR, 170171
NOT, 6364
OR, 5859, 222223
order of evaluation, 5961
optimizing performance, 295297
order
of clauses, 117
of evaluation, 5961, 80
ORDER BY clause
example, 115116
sorting by multiple columns, 4344
sorting by single column, 4143
OR operator, 5859, 222223
OUTER JOIN clause, 146147
outer joins, 145148
OUTPUT clause
DELETE statement, 188189
INSERT statement, 183184
UPDATE statement, 188189
overriding IDENTITY keyword, 196
P
parameters of stored procedures, 232235
parentheses, 61
PERCENT keyword, 3839
percent sign (%)
modulo operator, 80
wildcard character, 6668
performance
full-text searching, 161
improving, 295297
joins, 139
pi, returning, 94
Pi() function, 94
plus sign (+)
addition operator, 80
concatenation operator, 7477
populate.sql file, 22
portability, 83
precedence, order of, 80
predicates. See also keywords; operators
AND, 170
CONTAINS, 168172
definition of, 66
FREETEXT, 166168
IN, 6163
LIKE, 6566
NEAR, 170171
NOT, 6364
OR, 5859, 222223
primary keys
creating, 194195
definition of, 911, 130
and NULL values, 195
PRINT statement, 217
PRIOR keyword, 243
procedures. See stored procedures
processing
conditional processing
BEGIN statement, 223224
END statement, 223224
IF statement, 221223
WHILE statement, 225226
transaction processing
autocommits, 259, 261
COMMIT statement, 259
explained, 255257
ROLLBACK statement, 258259
savepoints, 260261
programming
comments, 220
conditional processing
BEGIN statement, 223224
END statement, 223224
IF statement, 221223
WHILE statement, 225226
explained, 213
variables
@ (at symbol), 214
assigning values to, 215216
converting to strings, 217
declaring, 214215
definition of, 214
rules and restrictions, 214
in statements, 218220
viewing contents of, 216217
Properties panel, 21
Q
queries
aggregate functions
Avg(), 9899
combining, 106107
Count(), 100101, 106
distinct values, 105
explained, 9798
with joins, 148150
Max(), 101102, 106
Min(), 102103
Sum(), 103104
AS keyword, 78
building incrementally, 128
combined queries
creating, 154157
duplicate rows, including/eliminating, 157158
explained, 153
sorting results of, 158159
concatenation (+) operator, 7477
cursors
closing, 241242
creating, 240241
deleting, 240241
explained, 239240
fetched data, 242245
opening, 241242
formatting, 122
GROUP BY clause, 110111, 115116
HAVING clause, 111114
joins
advantages of, 131
with aggregate functions, 148150
Cartesian products, 133137
creating, 132133
cross joins, 136
definition of, 129
equijoins, 137
guidelines, 150
inner joins, 137
multiple table joins, 138140
natural joins, 145
outer joins, 145148
performance considerations, 139
referential integrity, 131
self joins, 142144
WHERE clause, 133137
ORDER BY clause, 115116
sequence of clauses, 117
subqueries
as calculated fields, 123126
checking for existance with, 126128
correlated subqueries, 125126
definition of, 119
EXISTS keyword, 126128
filtering by, 119123
WHERE clause
IN operator, 6163
NOT operator, 6364
OR operator, 5859
order of evaluation, 5961
wildcard filtering
brackets ([ ]) wildcard, 6970
case sensitivity, 67
LIKE operator, 6566
NULL values, 68
percent sign (%) wildcard, 6668
tips for, 71
trailing spaces, 68
underscore (_) wildcard, 6869
query function, 271
quotation marks, 220
quotes, single (’), 53, 169, 220
R
Rand() function, 94
range of values, checking for, 54
ranking search results, 172173
RAW keyword, 266267
referential integrity, maintaining, 131
reformatting retrieved data, 207208
relational tables
aliases, 141142
explained, 129131
formatting, 130
joins
advantages of, 131
with aggregate functions, 148150
Cartesian products, 133137
creating, 132133
cross joins, 136
definition of, 129
equijoins, 137
guidelines, 150
inner joins, 137
multiple table joins, 138140
natural joins, 145
outer joins, 145148
performance considerations, 139
referential integrity, 131
self joins, 142144
WHERE clause, 133137
primary keys, 130
removing
access rights, 292293
views, 205
renaming
logins, 291
tables, 201
Replace() function, 85
reporting
on deleted data, 188189
on inserted data, 183184
on updated data, 188189
restrictions (views), 205
results, limiting, 3739
Results panel, 21
retrieved data
inserting, 180183
reformatting, 207208
retrieving data
columns
all columns, 3536
individual columns, 3133
multiple columns, 3334
JSON (JavaScript Object Notation) data, 273274
overview, 31
rows, 3637
XML (Extensible Markup Language) data, 264268
reusable views, 207
REVOKE statement, 292293
revoking access rights, 292293
Right() function, 85
RIGHT keyword, 146147
right outer joins, 147
ROLLBACK statement, 258259
rolling back statements, 258259
Round() function, 94
rounding numbers, 94
rows
counting, 100101
deleting
deleted data, reporting on, 188189
guidelines, 188189
syntax, 187188
duplicate rows in unions, including/eliminating, 157158
explained, 910
inserting
complete rows, 176179
list of changes, returning, 183184
multiple rows, 179180
retrieved data, 180183
retrieving, 3637
updating
guidelines, 188189
syntax, 185187
updated data, reporting on, 188189
RTrim() function, 7677, 85
rules (views), 205
running stored procedures, 230
S
sa (system administrator) login, 288
SAVE TRANSACTION statement, 260261
savepoints, 260261
scaling databases, 131
schemas (XML), 271
search criteria
comparison operators, 4950
multiple WHERE clauses
AND operator, 5758
IN operator, 6163
NOT operator, 6364
OR operator, 5859
order of evaluation, 5961
nonmatches, 53
NULL values, 5455
range of values, 54
single values, 5152
WHERE clause, 4950
wildcard filtering
brackets ([ ]) wildcard, 6970
case sensitivity, 67
LIKE operator, 6566
NULL values, 68
percent sign (%) wildcard, 6668
tips for, 71
underscore (_) wildcard, 6869
search results, ranking, 172173
search term weights, assigning, 173
searching
full-text searching
enabling, 162163
explained, 161162
full-text catalogs, 163166
full-text indexes, 164166
performance, 161
performing with CONTAINS, 168172
performing with FREETEXT, 166168
search results, ranking, 172173
search term weights, assigning, 173
SELECT
aggregate functions, 97107
ASC keyword, 4647
AS keyword, 78
asterisk (*) wildcard, 3536
clause ordering, 117
comparison operators, 4950
concatenation (+) operator, 7477
DESC keyword, 4446
DISTINCT keyword, 3637
EXISTS keyword, 126128
formatting, 122
FROM keyword, 3134
fully qualified table names, 3940
GROUP BY clause, 110111, 115116
HAVING clause, 111114
LIKE operator, 6566
multiple columns, retrieving, 3334
ORDER BY clause, 4144, 115116
overview, 31
PERCENT keyword, 3839
TOP keyword, 3739
WHERE clause. See WHERE clause
wildcard filtering, 6571
subqueries
as calculated fields, 123126
checking for existance with, 126128
correlated subqueries, 125126
definition of, 119
filtering by, 119123
wildcard filtering
* (asterisk), 6162
brackets ([ ]) wildcard, 6970
case sensitivity, 67
LIKE operator, 6566
percent sign (%) wildcard, 6668
tips for, 71
trailing spaces, 68
underscore (_) wildcard, 6869
for XML (Extensible Markup Language) data, 271272
security
access rights, 291293
explained, 287288
logins, renaming, 291
user accounts
creating, 290
deleting, 290
enabling/disabling, 290
user management, 289
SELECT statement
aggregate functions
Avg(), 9899
combining, 106107
Count(), 100101, 106
distinct values, 105
explained, 9798
with joins, 148150
Max(), 101102, 106
Min(), 102103
Sum(), 103104
ASC keyword, 4446
AS keyword, 78
asterisk (*) wildcard, 3536
batch processing, 218219
COLLATE keyword, 281283
combined queries
creating, 154157
duplicate rows, including/eliminating, 157158
explained, 153
sorting results of, 158159
comparison operators, 4950
concatenation (+) operator, 7477
DESC keyword, 4446
DISTINCT keyword, 3637
EXISTS keyword, 126128
FOR JSON AUTO clause, 273
formatting, 122
FOR XML clause, 264268
FROM clause, 133
FULL OUTER JOIN clause, 148
fully qualified table names, 3940
GROUP BY clause, 110111, 115116
HAVING clause, 111114
INNER JOIN clause, 137138
INSERT SELECT, 180183
joins
advantages of, 131
with aggregate functions, 148150
Cartesian products, 133137
creating, 132133
cross joins, 136
definition of, 129
equijoins, 137
guidelines, 150
inner joins, 137
multiple table joins, 138140
natural joins, 145
outer joins, 145148
performance considerations, 139
referential integrity, 131
self joins, 142144
WHERE clause, 133137
JSON PATH clause, 274
FROM keyword
retrieving individual columns, 3133
retrieving multiple columns, 3334
ORDER BY clause
example, 115116
sorting by multiple columns, 4344
sorting by single column, 4143
OUTER JOIN clause, 146147
overview, 31
PERCENT keyword, 3839
retrieving variable values with, 216
sequence of clauses, 117
subqueries
as calculated fields, 123126
checking for existance with, 126128
correlated subqueries, 125126
definition of, 119
filtering by, 119123
TOP keyword, 3739
UNION keyword, 154157
variables in, 219220
views
advantages of, 204205
with calculated fields, 210211
creating, 205
explained, 203204
filtering data with, 209
reformatting retrieved data with, 207208
removing, 205
reusable views, 207
rules and restrictions, 205
simplifying joins with, 206207
updating, 205, 211212
WHERE clause
AND operator, 5758
comparison operators. See comparison operators
IN operator, 6163
joins and, 133137
nonmatches, checking for, 53
NOT operator, 6364
NULL values, checking for, 5455
OR operator, 5859
order of evaluation, 5961
range of values, checking for, 54
single values, checking against, 5152
syntax, 4950
wildcard filtering
brackets ([ ]) wildcard, 6970
case sensitivity, 67
LIKE operator, 6566
NULL values, 68
percent sign (%) wildcard, 6668
tips for, 71
trailing spaces, 68
underscore (_) wildcard, 6869
selecting databases, 2425
self joins, 142144
semicolon (;), 33, 179180
sequences
of clauses, 117
collation sequences, 278280
of evaluation, 5961, 80
ServerProperty() function, 279
SET IDENTITY_INSERT statement, 177
SET statement, 186, 215216
setting up
access rights, 291292
full-text searching
full-text catalogs, 163166
full-text indexes, 164166
full-text search support, 162163
Sin() function, 94
sines, returning, 94
single quotes (’), 53, 169, 220
single values, checking against, 5152
sorting combined query results, 158159
sorting data
in ascending order, 4647
case sensitivity and, 46
in descending order, 4446
dictionary sort order, 46
grouping versus sorting, 114116
by multiple columns, 4344
by single column, 4143
Soundex() function, 8586
sp_columns stored procedure, 2728
sp_databases stored procedure, 26
sp_fulltext_database stored procedure, 162163
sp_helplogins stored procedure, 28, 289
SP_HELPTRIGGER stored procedure, 250
sp_helpuser stored procedure, 28
sp_rename stored procedure, 201
sp_server_info stored procedure, 28
sp_spaceused stored procedure, 28
sp_statistics stored procedure, 28
sp_tables stored procedure, 2627
spaces
trailing spaces, 68
trimming, 7677
SQL (Structured Query Language)
advantages of, 11
definition of, 11
SQL Server
advantages of, 13
client utilities, 1516, 18
connecting to, 1920
Database Engine Tuning Advisor, 296297
debugger, 218
documentation, 297
installing, 18
JSON (JavaScript Object Notation) data
functions, 275
retrieving data as JSON, 273274
obtaining, 17
server access, 1617
server software, 1415
SQL Server Express, 17
SQL Server Management Studio
locating, 18
navigating, 2022
overview, 1516
versions, 15
XML (Extensible Markup Language) data
retrieving, 264268
searching for, 271272
storing, 268270
support for, 263
SQL Server Express, 17
SQL Server Management Studio
locating, 18
navigating, 2022
overview, 1516
Sqrt() function, 94
square brackets ([ ]), 275
Square() function, 94
square roots, returning, 94
squares, returning, 94
statements
ALTER FULLTEXT, 165166
ALTER LOGIN, 290291
ALTER TABLE, 199200, 280, 291
ALTER TRIGGER, 249
autocommits, 259, 261
batches, 219
BEGIN, 223224
BREAK, 226
case conventions, 33
CLOSE, 241242
COMMIT, 259
CONTINUE, 226
CREATE FULLTEXT CATALOG, 163
CREATE FULLTEXT INDEX, 164
CREATE LOGIN, 290
CREATE PROCEDURE, 230231
CREATE TABLE
collation sequences, 280
DEFAULT keyword, 197198
default values, 197198
example, 269
IDENTITY keyword, 195197
NULL values, 193194
primary keys, 194195
syntax, 192193
CREATE TRIGGER, 248
CREATE VIEW, 205
DEALLOCATE, 240241
DECLARE, 214215, 233, 240241
DELETE
DELETE FROM, 188
DELETE triggers, 252
deleted data, reporting on, 188189
guidelines, 188189
syntax, 187190
DISABLE TRIGGER, 250
DROP FULLTEXT, 165166
DROP LOGIN, 290
DROP PROCEDURE, 231232
DROP TABLE, 201
DROP TRIGGER, 249
DROP VIEW, 205
ENABLE TRIGGER, 250
END, 223224
EXEC, 230
EXECUTE, 230
explicit commits, 259
FETCH, 242245
formatting, 193
GRANT, 291292
grouping, 223224
IF, 221223
INSERT
disabling, 175
INSERT SELECT, 180183
INSERT triggers, 251252
inserting complete rows, 176179
inserting multiple rows, 179180
inserting retrieved data, 180183
INTO keyword, 179
omitting columns from, 179
OUTPUT clause, 183184
overview, 175
returning list of changes, 183184
Unicode text, 284285
VALUES keyword, 177179
OPEN, 241242
PRINT, 217
REVOKE, 292293
ROLLBACK, 258259
rolling back, 258259
SAVE TRANSACTION, 260261
SELECT
aggregate functions, 97107
ASC keyword, 4647
AS keyword, 78
asterisk (*) wildcard, 3536
batch processing, 218219
clause ordering, 117
COLLATE keyword, 281283
combined queries, 153159
comparison operators, 4950
concatenation (+) operator, 7477
DESC keyword, 4446
DISTINCT keyword, 3637
EXISTS keyword, 126128
FOR JSON AUTO clause, 273, 274
formatting, 122
FOR XML clause, 264268
FROM clause, 133
FROM keyword, 3134
FULL OUTER JOIN clause, 148
fully qualified table names, 3940
GROUP BY clause, 110111, 115116
HAVING clause, 111114
INNER JOIN clause, 137138
INSERT SELECT, 180183
joins. See joins
LIKE operator, 6566
multiple columns, retrieving, 3334
ORDER BY clause, 4144, 115116
OUTER JOIN clause, 146147
overview, 31
PERCENT keyword, 3839
retrieving variable values with, 216
TOP keyword, 3739
UNION keyword, 154157
variables in, 219220
views, 203212
WHERE clause. See WHERE clause
wildcard filtering, 6571
SET, 186, 215216
SET IDENTITY_INSERT, 177
subqueries
as calculated fields, 123126
checking for existance with, 126128
correlated subqueries, 125126
definition of, 119
filtering by, 119123
triggers
AFTER triggers, 249
creating, 248
DELETE triggers, 252
disabling, 249250
dropping, 249
enabling, 249250
explained, 247
guidelines, 253254
INSERT triggers, 251252
INSTEAD OF triggers, 249
multistatement triggers, 252
trigger assignments, determining, 250
UPDATE triggers, 253
updating, 249
UPDATE
guidelines, 188189
syntax, 185187
UPDATE triggers, 253
updated data, reporting on, 188189
USE, 2425
variables in, 218220
WHILE, 225226
white space in, 33
stored procedures
advantages of, 228229
creating, 230231
definition of, 25
disadvantages of, 229
dropping, 231232
executing, 230
explained, 227228
intelligent stored procedures, 235238
parameters, 232235
sp_columns, 2728
sp_databases, 26
sp_fulltext_database, 162163
sp_helplogins, 28, 289
SP_HELPTRIGGER, 250
sp_helpuser, 28
sp_rename, 201
sp_server_info, 28
sp_spaceused, 28
sp_statistics, 28
sp_tables, 2627
storing XML (Extensible Markup Language) data, 268270
Str() function, 85
strings
converting variables to, 217
empty strings, 194
text manipulation functions, 8486
Structured Query Language. See SQL (Structured Query Language)
subqueries
as calculated fields, 123126
checking for existance with, 126128
correlated subqueries, 125126
definition of, 119
EXISTS keyword, 126128
filtering by, 119123
SubString() function, 85
subtraction (-) operator, 80
Sum() function, 103104
summarizing data
Avg() function, 9899
Count() function
DISTINCT keyword, 106
explained, 100101
explained, 9798
Max() function
DISTINCT keyword, 106
explained, 101102
Min() function, 102103
Sum() function, 103104
at symbol (@), 214, 233
SYS database, 289
system administrator (sa) login, 288
system administrators, 19
T
tables
aliases
explained, 141142
naming, 107
columns
aliases, 7778
average value of, returning, 9899
deleting, 187190
explained, 78
fully qualified column names, 133
identity columns, 28
maximum value, returning, 101102
minimum value, returning, 102103
omitting from INSERT operations, 179
returning list of, 2728
sorting by, 4144
totals, returning, 103104
updating, 185187
creating
basic table creation, 192193
DEFAULT keyword, 197198
default values, 197198
explained, 191
IDENTITY keyword, 195197
NULL values, 193194
primary keys, 194195
data. See data
datatypes, 78
definition of, 67
deleting, 201
different tables, combining, 159
dropping, 201
foreign keys, 11, 130
fully qualified table names, 3940
joins
advantages of, 131
with aggregate functions, 148150
Cartesian products, 133137
creating, 132133
cross joins, 136
definition of, 129
equijoins, 137
guidelines, 150
inner joins, 137
multiple table joins, 138140
natural joins, 145
outer joins, 145148
performance considerations, 139
referential integrity, 131
self joins, 142144
simplifying with views, 206207
WHERE clause, 133137
primary keys
creating, 194195
definition of, 911, 130
and NULL values, 195
relational tables, 129131
renaming, 201
returning list of, 2627
rows
counting, 100101
deleting, 187188
duplicate rows in unions, including/eliminating, 157158
explained, 910
inserting, 176183
updating, 185187
sample tables, creating, 2223
updating, 199200
views
advantages of, 204205
with calculated fields, 210211
creating, 205
explained, 203204
filtering data with, 209
reformatting retrieved data with, 207208
removing, 205
reusable views, 207
rules and restrictions, 205
simplifying joins with, 206207
updating, 205, 211212
Tan() function, 94
tangents, returning, 94
testing calculations, 80
text
aggregate functions and
Max(), 102
Min(), 103
full-text searching
enabling, 162163
explained, 161162
full-text catalogs, 163166
full-text indexes, 164166
performance, 161
performing with CONTAINS, 168172
performing with FREETEXT, 166168
search results, ranking, 172173
text manipulation functions, 84
time manipulation functions, 8794
TOP keyword, 3739
totals (column), returning, 103104
trailing spaces, 68
transaction processing
autocommits, 259, 261
COMMIT statement, 259
explained, 255257
ROLLBACK statement, 258259
savepoints, 260261
triggers
AFTER triggers, 249
creating, 248
DELETE triggers, 252
disabling, 249250
dropping, 249
enabling, 249250
explained, 247
guidelines, 253254
INSERT triggers, 251252
INSTEAD OF triggers, 249
multistatement triggers, 252
trigger assignments, determining, 250
UPDATE triggers, 253
updating, 249
trigonometric functions, 94
trimming spaces, 7677
tuning performance, 295297
U
underscore (_), 6869
undoing statements, 258259
Unicode, 283285
UNION keyword, 154157
UPDATE statement
guidelines, 188189
syntax, 185187
UPDATE triggers, 253
updated data, reporting on, 188189
UPDATE triggers, 253
updated data, reporting on, 188189
updating
data
guidelines, 188189
syntax, 185187
updated data, reporting on, 188189
indexes, 165
tables, 199200
triggers, 249
views, 205, 211212
Upper() function, 8485
uppercase, converting strings to, 85
USE statement, 2425
user accounts
creating, 290
deleting, 290
enabling/disabling, 290
users
access rights, 291293
logins, renaming, 291
managing, 289
user accounts
creating, 290
deleting, 290
enabling/disabling, 290
utilities, Database Engine Tuning Advisor, 296297
V
value function, 271
values
assigning to variables, 215216
NULL values
aggregate functions and. See aggregate functions
checking for, 5455
compared to empty strings, 194
CREATE TABLE statement, 193194
null value elimination warning, 150
primary keys and, 195
wildcards and, 68, 187
VALUES keyword, 177179
variables
@ (at symbol), 214
assigning values to, 215216
converting to strings, 217
declaring, 214215
definition of, 214
rules and restrictions, 214
in statements, 218220
viewing contents of, 216217
versions of SQL Server, 15
viewing variable contents, 216217
views
advantages of, 204205
with calculated fields, 210211
creating, 205
dropping, 205
explained, 203204
filtering data with, 209
reformatting retrieved data with, 207208
removing, 205
reusable views, 207
rules and restrictions, 205
simplifying joins with, 206207
updating, 205, 211212
W
warnings, null value elimination warning, 150
weight values (search), 173
WHERE clause
AND operator, 5758
brackets ([ ]) wildcard, 6970
comparison operators
BETWEEN operator, 54
equality operator (=), 51
IS NULL operator, 5455
less than operator (<), 52
nonequality operator (<>), 53
nonequality operator (!=), 53
table of, 4950
IN operator, 6163
joins, 133137
LIKE operator, 6566
nonmatches, checking for, 53
NOT operator, 6364
NULL values, checking for, 5455
OR operator, 5859
order of evaluation, 5961
percent sign (%) wildcard, 6668
range of values, checking for, 54
single values, checking against, 5152
subqueries
as calculated fields, 123126
checking for existance with, 126128
correlated subqueries, 125126
definition of, 119
EXISTS keyword, 126128
filtering by, 119123
syntax, 4950
underscore (_) wildcard, 6869
views, 209
WHILE statement, 225226
white space, 33
wildcard filtering
* (asterisk), 6162
brackets ([ ]) wildcard, 6970
case sensitivity, 67
LIKE operator, 6566
percent sign (%) wildcard, 6668
tips for, 71
trailing spaces, 68
underscore (_) wildcard, 6869
X-Y-Z
XML (Extensible Markup Language) data
retrieving, 264268
searching for, 271272
storing, 268270
support for, 263
XML schemas, 271
XPath, 268, 272
XQuery, 269, 272
Year() function, 87, 9293
Code Snippets